/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package orest.common.dao;
import java.util.*;
import java.sql.*;
import orest.common.response.Response;
import orest.common.valueobject.NeedForAchieveVal;
import orest.common.util.DataBase;
import orest.common.util.Tools;
/**
 *
 * @author admin
 */
public class NeedForAchieveDao {
    
   public NeedForAchieveDao() {
    }
    
    /**
     * Return a boolean value to indentify the operator successful or failure.
     * 
     * This function will complete the task of insert user information
     * into the ReportOrder Table.
     *
     * @param obj   ReportOrder instance.
     * @return a boolean value to indentify the operator successful or failure.
     */
    public boolean addNeedForAchieve(NeedForAchieveVal obj){
    	boolean result = true;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        Tools tool = new Tools();
        String sql = "";
        try{
                   sql = " insert into T_NeedForAchieve(ID, Title, Keywords,NewEnergyClass," +
                         " Item_16,NeedReason,Cooperate, PlanInvest,TechSatus,TechNeed, NeederType,UnitName,Item_62,Province,RegMoney,StaffSum,SaleMoney,UnitIntro,Addr, LinkMan,Zip,Tel, " +
                         " Fax, Email, UserID,SubmitDate,IfProcess)";
                  sql += " values('" + 
                          obj.getID() + "', '" +
                          obj.getTitle() +"', '" +
                          obj.getKeywords() + "', '" +
                          obj.getNewEnergyClass() + "', '" +
                          obj.getItem_16() + "', '" +
                          obj.getNeedReason() + "', '" +
                          obj.getCooperate() + "', '" +
                          obj.getPlanInvest() + "', '" +
                          obj.getTechSatus() + "', '" +
                          obj.getTechNeed() + "', '" +
                          obj.getNeederType() + "', '" +
                          obj.getUnitName() + "', '" +
                          obj.getItem_62() + "', '" +
                          obj.getProvince() + "', " +
                          obj.getRegMoney() + ", '" +
                          obj.getStaffSum() + "', " +
                          obj.getSaleMoney() + ", '" +
                          obj.getUnitIntro() + "', '" +
                          obj.getAddr()+"', '"+                      
                          obj.getLinkMan()+"', '"+
                          obj.getZip()+"', '"+
                          obj.getTel()+"', '"+                          
                          obj.getFax()+"', '"+
                          obj.getEmail()+"', '"+
                          obj.getUserID()+"','"+
                          tool.gettimestamp()+"','"+
                          obj.getIfProcess()+"')";
                          
            conn = db.getSysConnectionPool(); 
            pstmt = conn.prepareStatement (sql);
            System.out.println("sql is"+sql);
            pstmt.execute();
            return result;
        }
        catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
            e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                	e.printStackTrace();
                }
            }
            
            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                	e.printStackTrace();
                }
            }
        }
    } 



     public boolean findNeedforAchieveByCondition(Response rp,String condtion,
                                                                int pageNo,
                                                                int pageRecord,
                                                                int total) {

        boolean result = true;
      //  boolean firsthalf = true;
        ArrayList al = new ArrayList();
        int iPrePageCount ;
        int tempRow = 0;
        Tools tool = new Tools();

        /* number of record in page */
        int startRow = (pageNo -1) * pageRecord;
        int endRow = pageNo * pageRecord;

        if(endRow > total)
            endRow = total;




        tempRow = 1;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top  " + Integer.toString(endRow);
        String sql = "ID,Title,UserID,SubmitDate,IfProcess from T_NeedForAchieve where  " + condtion;



            sql += " order by SubmitDate desc";
        /* sql string end */

        /*
           count plan production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            System.out.println(rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                if(tempRow > startRow) {
                 NeedForAchieveVal needVal = new NeedForAchieveVal();
                 needVal.setID(rs.getString("ID"));
                 needVal.setTitle(rs.getString("Title"));
                 needVal.setUserID(rs.getString("UserID"));
                 needVal.setSubmitDate(rs.getDate("SubmitDate"));
                 needVal.setIfProcess(rs.getString("IfProcess"));
                 
                
                 al.add (needVal);
                }

                else
                    tempRow++;
            }


                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("When get the needforachieve info, have a exception!" +
                                " The class name is NeedForAchieveDao." + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getMyConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is NeedForAchieveDao and the " +
                                 "function is findNeedforAchieveByCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                 //   pstmt = conn.prepareStatement ("set rowcount 0");
                 //   pstmt.execute ();
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of NeedForAchieveDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of NeedForAchieveDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of NeedForAchieveDao and" +
                            " the function is findNeedforAchieveByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }


     public long countNeedforAchieveByCondition(String condition, Response rp) {

        ArrayList al = new ArrayList();
        long total = 0;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select count(1) as cnt from T_NeedForAchieve where  " + condition;
        /* sql string end */
         System.out.println("sql is"+sql);
        /*
           count  production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                total = rs.getLong("cnt");
            }
            return total;
        }
        catch(SQLException e){
            System.out.println("When countNeedforachieveByCondition, have a exception!" +
                                " The class name is NeedForAchieveDao." + e);
            return 0;
        }
        catch(Exception e){
            System.out.println ("When call the countNeedforachieveByCondition, have a"+
                                 "exception occured!   ");
            e.printStackTrace();

            return 0;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of NeedForAchieveDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of NeedForAchieveDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of NeedForAchieveDao and" +
                            " the function is countNeedforachieveByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

     public boolean findNeedforAchieveByPrk(Response rp, String id){
        boolean result = true;
        ArrayList al = new ArrayList();

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select * from T_NeedForAchieve where  ID = ?";
        /* sql string end */

        /*
           get all of value of the production.
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                 al.add (new NeedForAchieveVal(
                    rs.getString("ID"),
                    rs.getString("Title"),
                    rs.getString("Keywords"),
                    rs.getString("NewEnergyClass"),
                    rs.getString("Item_16"),
                    rs.getString("NeedReason"),
                    rs.getString("Cooperate"),
                    rs.getString("PlanInvest"),
                    rs.getString("TechSatus"),
                    rs.getString("TechNeed"),
                    rs.getString("NeederType"),
                    rs.getString("UnitName"),
                    rs.getString("Item_62"),
                    rs.getString("Province"),
                    rs.getInt("RegMoney"),
                    rs.getString("StaffSum"),
                    rs.getInt("SaleMoney"),
                    rs.getString("UnitIntro"),
                    rs.getString("Addr"),
                    rs.getString("Linkman"),
                    rs.getString("Zip"),                    
                    rs.getString("Tel"),                   
                    rs.getString("Fax"),
                    rs.getString("Email"),
                    rs.getString("UserID"),
                    rs.getDate("SubmitDate"),
                    rs.getString("IfProcess")
                    ));
            }
            rp.setValObjs (al);
            return result;
        }
        catch(SQLException e){
            System.out.println ("When get technology require by class," +
                    " there is a exception occured!! ");
            System.out.println ("The class is NeedForAchieveDao! " +
                                 "The function is findNeedforAchieveByPrk! " +
                                 e);
            result = false;
            return result;
        }
        catch(Exception e){
            System.out.println ("When call the getMyConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is NeedForAchieveDao and the " +
                                 "function is findNeedforAchieveByPrk! " + e);
            result = false;
            return result;
        }
        finally{
            /* close the prestatement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of NeedForAchieveDao " +
                            "and the function is findNeedforAchieveByPrk, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of NeedForAchieveDao and " +
                            "the function is findNeedforAchieveByPrk, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of NeedForAchieveDao and" +
                            "the function is findNeedforAchieveByPrk, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

      public boolean setIfProcess(String id,String ifprocess){
    	boolean result = true;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        Tools tool = new Tools();
        String sql = "";
        try{
                   sql = " update  T_NeedForAchieve set ";
                   sql += "IfProcess = '"+ ifprocess +"' ";
                  sql += "where ID = '" + id + "'";
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            pstmt.execute();
            return result;
        }
        catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
            e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                	e.printStackTrace();
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                	e.printStackTrace();
                }
            }
        }
    }

       public boolean deleteNeedForAchieve(String id) {
    	boolean result = true;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();

        String sql = "delete from  T_NeedForAchieve where ID = '" + id + "'";

        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            pstmt.execute();
            return result;
           }
        catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
        	e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                	e.printStackTrace();
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                	e.printStackTrace();
                }
            }
        }
    }


       public boolean deleteNeedForAchieve_batch(String[] id) {
        boolean flag = false;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();


        String sql = "delete from T_NeedForAchieve where ID=?";

       try {
            conn = db.getSysConnectionPool ();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < id.length; i++) {
                pstmt.setString(1, id[i]);
                // ʹ������
                pstmt.addBatch();
             }
            // ִ������
           int[] result = pstmt.executeBatch();
           if (result[0] > 0) {
                flag = true;

            }
                return flag;
            }
            catch(SQLException e){
                e.printStackTrace();
                flag = false;
                return flag;
            }
            catch(Exception e){
                e.printStackTrace();
                flag = false;
                return flag;
            }
            finally {
                if(null != pstmt){
                    try{
                        pstmt.close ();
                        pstmt = null;
                    }
                    catch(SQLException e){
                        e.printStackTrace();
                     }
                    }

                     /* release the conn */
                     if(null != conn){
                     try{
                        conn.close();
                        conn = null;
                         db = null;
                        }
                        catch(Exception e){
                            e.printStackTrace();
                        }
                    }
                }


 }

      public boolean SetIsCheckNeedForAchieve_Batch(String ifcheck,String[] id) {
        boolean flag = false;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();


        String sql = "update T_NeedForAchieve set IfProcess='"+ifcheck +"' where ID=?";

       try {
            conn = db.getSysConnectionPool ();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < id.length; i++) {
                pstmt.setString(1, id[i]);
                // ʹ������
                pstmt.addBatch();
             }
            // ִ������
           int[] result = pstmt.executeBatch();
           if (result[0] > 0) {
                flag = true;

            }
                return flag;
            }
            catch(SQLException e){
                e.printStackTrace();
                flag = false;
                return flag;
            }
            catch(Exception e){
                e.printStackTrace();
                flag = false;
                return flag;
            }
            finally {
                if(null != pstmt){
                    try{
                        pstmt.close ();
                        pstmt = null;
                    }
                    catch(SQLException e){
                        e.printStackTrace();
                     }
                    }

                     /* release the conn */
                     if(null != conn){
                     try{
                        conn.close();
                        conn = null;
                         db = null;
                        }
                        catch(Exception e){
                            e.printStackTrace();
                        }
                    }
                }


 }

}
